using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;

namespace Sanchay
{
    public partial class SpareSalesRegister : Form
    {
        AutoCompleteStringCollection autoCompleteStrings;
        string temp = "";
        bool flag = false;
        bool invoiceFlag = false;

        public SpareSalesRegister()
        {
            InitializeComponent();
        }

        private void SpareSalesRegister_Load(object sender, EventArgs e)
        {
            ResetEntryGrid();
            autoCompleteStrings = new AutoCompleteStringCollection();
            autoCompleteStrings.Add("");
            AutoSuggestForCustomer();
        }

        private void AutoSuggestForCustomer()
        {
            // Auto Complete for Supplier
            DataAccess da = new DataAccess();
            SqlConnection conn = da.conn;
            SqlCommand cmd = new SqlCommand("SELECT Name FROM Customer", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                txtCustomer.AutoCompleteCustomSource.Add(dr.GetString(0));
            }
            txtCustomer.AutoCompleteMode = AutoCompleteMode.Suggest;
            txtCustomer.AutoCompleteSource = AutoCompleteSource.CustomSource;
            conn.Close();
        }

        private void ResetEntryGrid()
        {
            for (int i = 0; i < 10; i++)
            {
                dgvSalesInvoice.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
                dgvSalesInvoice.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
                dgvSalesInvoice.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgvSalesInvoice.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgvSalesInvoice.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvSalesInvoice.Columns[5].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
                
                dgvSalesInvoice.Rows.Add();
                //dgvSalesInvoice.Rows[i].Cells[0].Value = (_srNo).ToString();
                dgvSalesInvoice.Rows[i].Cells[0].Value = "";
                dgvSalesInvoice.Rows[i].Cells[1].Value = "";
                dgvSalesInvoice.Rows[i].Cells[2].Value = "";
                dgvSalesInvoice.Rows[i].Cells[3].Value = "";
                dgvSalesInvoice.Rows[i].Cells[4].Value = "";
                dgvSalesInvoice.Rows[i].Cells[5].Value = "";
                //_srNo += 1;
                if (dgvSalesInvoice.Rows.Count == 10)
                    dgvSalesInvoice.AllowUserToAddRows = false;
            }
            //dgvSalesInvoice.Rows[0].Cells[0].Selected = true; // Foucus to the first cell of the Grid
        }

        private void ClearEntryGrid()
        {
            for (int i = 0; i < 10; i++)
            {
                dgvSalesInvoice.Rows.Clear();

            }

        }

        private void RefreshSummeryGrid()
        {
            DataAccess da = new DataAccess();

            string query = "SELECT [Parts No.], [Parts Name], [Quantity],[Unit Price], [Amount] from vw_SummeryGrid WHERE InvoiceNo='" + txtSalesInvoiceNo.Text + "'";

            DataTable dt = da.SelectCommand(query);

            dgvInvoiceSummery.DataSource = dt.DefaultView;
            dgvInvoiceSummery.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            dgvInvoiceSummery.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            dgvInvoiceSummery.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
            dgvInvoiceSummery.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;

            dgvInvoiceSummery.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter;
            dgvInvoiceSummery.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight;
            dgvInvoiceSummery.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight;
            //dgvInvoiceSummery.Columns[0].FillWeight = 4;
            dgvInvoiceSummery.Columns[0].FillWeight = 17;
            dgvInvoiceSummery.Columns[1].FillWeight = 45;
            dgvInvoiceSummery.Columns[2].FillWeight = 8;
            dgvInvoiceSummery.Columns[3].FillWeight = 15;
            dgvInvoiceSummery.Columns[4].FillWeight = 15;
            //GiveSerialNumbersToSummeryGrid();
        }

        private void dgvSalesInvoice_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
        {
            TextBox t = (TextBox)e.Control;
            t.TextChanged += new EventHandler(t_TextChanged);
        }

        void t_TextChanged(object sender, EventArgs e)
        {
            TextBox t = (TextBox)sender;
            if (dgvSalesInvoice.CurrentCell.ColumnIndex == 0)  // to prevent autosuggest on other textboxes in the grid
            {

                if (t.Text.Length > 3)
                {
                    return;
                }
                if (t.Text.Length == 3)
                {
                    if (temp != t.Text)
                        flag = false;

                    if (flag == false)
                    {
                        //SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\db.mdf;Integrated Security=True;User Instance=True");
                        DataAccess da= new DataAccess();

                        SqlConnection conn = da.conn;
                        
                        try
                        {

                            string query = "SELECT PartsNo FROM SpareParts WHERE PartsNo LIKE '" + t.Text + "%'";

                            SqlDataReader dr;
                            SqlCommand cmd = new SqlCommand(query, conn);
                            conn.Open();
                            dr = cmd.ExecuteReader();

                            while (dr.Read())
                            {

                                autoCompleteStrings.Add(dr.GetString(0));
                            }
                            t.AutoCompleteMode = AutoCompleteMode.Suggest;
                            t.AutoCompleteSource = AutoCompleteSource.CustomSource;
                            t.AutoCompleteCustomSource = autoCompleteStrings;
                            temp = t.Text;
                            flag = true;
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("An error occured. Please try again.");
                        }
                        finally
                        {

                            conn.Close();
                        }
                    }
                }
            }
        }

        private void dgvSalesInvoice_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.ColumnIndex == 0 && dgvSalesInvoice.Rows[e.RowIndex].Cells[0].Value.ToString() != "")
                {
                    DataAccess da = new DataAccess();
                    string partsNo = dgvSalesInvoice.Rows[e.RowIndex].Cells[0].Value.ToString();
                    //SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\db.mdf;Integrated Security=True;User Instance=True");
                    SqlConnection conn = da.conn;
                    if (CheckDuplicateEntry(partsNo) == true)
                    {
                        MessageBox.Show("Duplicate Entry");
                        dgvSalesInvoice.Rows[e.RowIndex].Cells[e.ColumnIndex].Value = "";
                        //dgvSalesInvoice.CurrentCell = dgvSalesInvoice.Rows[e.RowIndex].Cells[e.ColumnIndex];
                        //dgvSalesInvoice.BeginEdit(true);

                        return;
                    }
                    string query = "SELECT PartsName,CurrentMRP FROM SpareParts WHERE PartsNo='" + partsNo + "'";
                    string query1 = "SELECT AvailableQty From vwCurrentStock WHERE PartsNo='" + partsNo + "'";
                    DataTable dt =da.SelectCommand(query1);
                    SqlDataReader dr;
                    SqlCommand cmd = new SqlCommand(query, conn);
                    autoCompleteStrings = new AutoCompleteStringCollection();
                    conn.Open();
                    dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        dgvSalesInvoice.Rows[e.RowIndex].Cells[1].Value = dr.GetString(0);
                        dgvSalesInvoice.Rows[e.RowIndex].Cells[4].Value = dr.GetDecimal(1);
                        
                    }
                    conn.Close();
                    int availableQty = GetAvailableQty(da, partsNo);
                    if (availableQty == -1)
                    {
                        availableQty = 0;
                    }
                    dgvSalesInvoice.Rows[e.RowIndex].Cells[2].Value = availableQty;
                }
                if (e.ColumnIndex == 3 || e.ColumnIndex == 4 || e.ColumnIndex == 5)
                {

                    int itemQuantity;
                    decimal unitPrice;
                    decimal totalAmount;

                    int.TryParse(dgvSalesInvoice.Rows[e.RowIndex].Cells[3].Value.ToString(), out itemQuantity);
                    decimal.TryParse(dgvSalesInvoice.Rows[e.RowIndex].Cells[4].Value.ToString(), out unitPrice);
                    decimal.TryParse(dgvSalesInvoice.Rows[e.RowIndex].Cells[5].Value.ToString(), out totalAmount);

                    dgvSalesInvoice.Rows[e.RowIndex].Cells[5].Value = decimal.Round(itemQuantity * unitPrice, 2);

                }
            }
            catch (Exception ex)
            {

            }
        }

        private bool CheckDuplicateEntry(string partsNo)
        {
            int count = 0;
            for (int i = 0; i < dgvSalesInvoice.Rows.Count - 1; i++)
            {
                if (dgvSalesInvoice.Rows[i].Cells[0].Value.ToString() != "")
                    count += 1;


            }
            for (int j = 0; j < count - 1; j++)
            {
                if (dgvSalesInvoice.Rows[j].Cells[0].Value.ToString() == partsNo)
                    return true;
            }
            return false;
        }

        private void pbSave_Click(object sender, EventArgs e)
        {
            if (txtSalesInvoiceNo.Text != "")
            {
                DataAccess da = new DataAccess();

                //Preventing duplicate Sales Invoice
                if (invoiceFlag == false)
                {
                    AddCustomerIfNotExists();
                    if (InsertSalesInvoiceDetails(da) != 0)
                    {
                        invoiceFlag = true;
                    }
                }
                UpdateInventory(da);
                RefreshSummeryGrid();
                // Showing Invoice Item Detail summery (saved only) into another grid
                ClearEntryGrid();
                ResetEntryGrid();
            }
            else
            {
                txtSalesInvoiceNo.BackColor = Color.Pink;
                txtSalesInvoiceNo.Focus();
            }
        }

        private void AddCustomerIfNotExists()
        {
            DataAccess da = new DataAccess();
            bool isCustomerExists = CheckIfCustomerExists(da);
            if (isCustomerExists == false)
            {
                string query = "INSERT INTO Customer values ('" + txtCustomer.Text + "' , '', '')";
                try
                {
                    if (txtCustomer.Text != "")
                    {
                        da.DoCommand(query);
                    }
                }
                catch (Exception ex)
                {

                }

            }
        }

        private bool CheckIfCustomerExists(DataAccess da)
        {
            bool isCustomerExists;
            string query = "SELECT Name FROM Customer WHERE Name ='" + txtCustomer.Text + "'";
            DataTable dt = da.SelectCommand(query);
            if (dt.Rows.Count != 0)
            {
                isCustomerExists = true;
            }
            else
            {
                isCustomerExists = false;
            }
            return isCustomerExists;
        }

        private int InsertSalesInvoiceDetails(DataAccess da)
        {
            string query;
            // SpareParts Purchase Invoice No
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("InvoiceNo", txtSalesInvoiceNo.Text);
            param[1] = new SqlParameter("InvoiceDate", dtpInvoiceDate.Value.ToShortDateString());
            param[2] = new SqlParameter("CustomerId", GetCustomerIdByName(da, txtCustomer.Text));
            
            query = "INSERT INTO SpareSalesRegister (InvoiceNo, InvoiceDate, CustomerId) VALUES (@InvoiceNo, @InvoiceDate, @CustomerId)";
            return da.DoCommand(query, param);
        }

        private int GetCustomerIdByName(DataAccess da, string CustomerName)
        {
            if (txtCustomer.Text != "")
            {
                string query = "SELECT CustomerId FROM Customer WHERE Name='" + CustomerName + "'";
                DataTable dt = da.SelectCommand(query);
                return (int)dt.Rows[0][0];
            }
            return 1;
        }

        private void UpdateInventory(DataAccess da)
        {
            string query;
            try
            {
                for (int i = 0; i < dgvSalesInvoice.Rows.Count - 1; i++)
                {
                    if (dgvSalesInvoice.Rows[i].Cells[0].Value.ToString() != "")
                    {
                        int currentQty = Convert.ToInt16(dgvSalesInvoice.Rows[i].Cells[3].Value);
                        int rowsAffacted = 0;
                        if (dgvSalesInvoice.Rows[i].Cells[0].Value.ToString() != "")
                        {
                            string partsNo = dgvSalesInvoice.Rows[i].Cells[0].Value.ToString();
                            int availableQty = GetAvailableQty(da, partsNo);
                            if (availableQty != -1)// If not Null
                            {
                                query = "UPDATE InventoryMaster SET isCurrent=0 WHERE PartsNo='" + partsNo + "' AND isCurrent=1";
                                da.DoCommand(query);
                            }
                            else
                            {
                                availableQty = 0;
                            }

                            // Sales Invoice Item Details
                            SqlParameter[] param = new SqlParameter[9];
                            param[0] = new SqlParameter("PartsNo", partsNo);
                            param[1] = new SqlParameter("Qty", currentQty);
                            param[2] = new SqlParameter("AvailableQty", availableQty-currentQty);
                            param[3] = new SqlParameter("TransactionDate", DateTime.Now);
                            param[4] = new SqlParameter("isCurrent", 1);
                            param[5] = new SqlParameter("TransactionType", 2);
                            param[6] = new SqlParameter("InvoiceNo", txtSalesInvoiceNo.Text);
                            param[7] = new SqlParameter("UnitPrice", Convert.ToDouble(dgvSalesInvoice.Rows[i].Cells[4].Value));
                            param[8] = new SqlParameter("TotalPrice", Convert.ToDouble(dgvSalesInvoice.Rows[i].Cells[5].Value));

                            query = "INSERT INTO InventoryMaster (PartsNO, Qty, AvailableQty, TransactionDate,isCurrent,TransactionType,InvoiceNo,UnitPrice,TotalPrice) VALUES (@PartsNO, @Qty, @AvailableQty, @TransactionDate,@isCurrent,@TransactionType,@InvoiceNo,@UnitPrice,@TotalPrice)";
                            rowsAffacted = da.DoCommand(query, param);
                            //toolStripStatusLabel.Text = rowsAffacted.ToString() + " Parts added successfully.";

                        }

                    }
                }
            }

            catch (Exception ex)
            {

            }

        }

        private int GetAvailableQty(DataAccess da, string partsNo)
        {
            string query = "SELECT AvailableQty FROM InventoryMaster WHERE PartsNo='" + partsNo + "' AND isCurrent=1";
            DataTable dt = da.SelectCommand(query);
            if (dt.Rows.Count != 0)
            {
                return (int)dt.Rows[0][0];
            }
            else
            {
                return -1;
            }
        }

        private void txtSalesInvoiceNo_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                CheckDuplicateInvoice();
            }
        }

        private void txtSalesInvoiceNo_Leave(object sender, EventArgs e)
        {
            CheckDuplicateInvoice();
        }

        private void CheckDuplicateInvoice()
        {
            if (txtSalesInvoiceNo.Text != "")
            {
                DataAccess da = new DataAccess();
                string query = "SELECT InvoiceNo FROM SpareSalesRegister WHERE InvoiceNo='" + txtSalesInvoiceNo.Text + "'";
                SqlCommand cmd = new SqlCommand(query, da.conn);
                da.conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                if (dr.HasRows == true)
                {
                    da.conn.Close();
                    DataTable dt = FillCustomerNameAndInvoiceDate(da, txtSalesInvoiceNo.Text);
                    txtCustomer.Text = (string)dt.Rows[0][1];
                    dtpInvoiceDate.Value = (DateTime)dt.Rows[0][0];
                    RefreshSummeryGrid();
                }
            }
        }

        private DataTable FillCustomerNameAndInvoiceDate(DataAccess da, string invoiceNo)
        {
            string query = "Select InvoiceDate, Name FROM vw_GetCustomerNameFromSalesInvoice WHERE InvoiceNo='" + invoiceNo + "'";
            DataTable dt = da.SelectCommand(query);
            return dt;
        }

        private void dgvSalesInvoice_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 1 || e.ColumnIndex==2)
            {
                SendKeys.Send("{TAB}");
            }

        }

        private void pbNew_Click(object sender, EventArgs e)
        {
            dgvInvoiceSummery.DataSource = "";
            ClearEntryGrid();
            ResetEntryGrid();
            txtSalesInvoiceNo.Text = "";
            dtpInvoiceDate.ResetText();
            txtCustomer.Text = "";
            invoiceFlag = false;
        }

        private void pbClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void txtSalesInvoiceNo_TextChanged(object sender, EventArgs e)
        {
            if (txtSalesInvoiceNo.Text.Length > 0)
            {
                txtSalesInvoiceNo.BackColor = Color.White;
            }
        }


       

    }
}